-- Script n°14: Alters the "photo" tables by dropping the location columns related to the old enumerations.
-- Moreover, adds the constraints (unicity and foreign key) on the new "cityid" column.
-- @author Clément HELIOU (clement.heliou@gmail.com)
-- @since 1.0

BEGIN;

-- Drop the columns.
ALTER TABLE photo DROP COLUMN city;
ALTER TABLE photo DROP COLUMN department;
ALTER TABLE photo DROP COLUMN country;

-- Add the constraints.
ALTER TABLE photo ALTER COLUMN cityid SET NOT NULL;
ALTER TABLE photo
   ADD CONSTRAINT fk_photo_cityl FOREIGN KEY (cityid)
      REFERENCES city (id)
      ON DELETE RESTRICT ON UPDATE RESTRICT;


END;